/* eslint-disable class-methods-use-this */
import { Connection } from 'mariadb'

import { DbColumn } from '../../models/dbColumn'
import { DbForeignKey } from '../../models/dbForeignKey'
import { DbIndex } from '../../models/dbIndex'
import { DbModel } from '../../models/dbModel'
import { DbTable } from '../../models/dbTable'
import { DbOptions } from '../../services/dbOptions'
import { DbImportProvider } from '../core/dbImportProvider'
import { MariaDbUtil } from './mariaDbUtil'

export class MariaDbImportProvider extends DbImportProvider {
	tableMap: Map<string, DbTable>

	static DATA_TYPES_WITH_LENGTH = ['enum', 'char', 'varchar', 'decimal', 'binary', 'varbinary']

	static REGEX_LEN_EXPR = /\([\s,\d]*\)/g

	public constructor() {
		super()
		this.tableMap = new Map<string, DbTable>()
	}

	public async getModel(dbOptions: DbOptions): Promise<DbModel> {
		const conn = await MariaDbUtil.getConnection(dbOptions)

		const model: DbModel = { tables: [] }
		try {
			await this.getModelInternal(conn, model)
		} finally {
			conn.end()
		}

		return model
	}

	private async getModelInternal(conn: Connection, model: DbModel) {
		await this.RenderColumn(conn, model)
		await this.RenderPKAndIndex(conn)
		await this.RenderForeignKey(conn)
	}

	private createTable(name: string): DbTable {
		return { name, columns: [], foreignKeys: [], indexes: [] }
	}

	private async RenderPKAndIndex(conn: Connection) {
		const sSQL: string = `SELECT S.TABLE_NAME
        , S.INDEX_NAME
        , S.SEQ_IN_INDEX
        , S.COLUMN_NAME
        , S.NON_UNIQUE
        , S.INDEX_TYPE
        , TC.CONSTRAINT_TYPE
      FROM information_schema.STATISTICS S
LEFT JOIN information_schema.TABLE_CONSTRAINTS TC
        ON S.TABLE_SCHEMA = TC.TABLE_SCHEMA
      AND S.TABLE_NAME = TC.TABLE_NAME
      AND S.INDEX_NAME = TC.CONSTRAINT_NAME
    WHERE S.TABLE_SCHEMA = DATABASE()
		  AND IFNULL(TC.CONSTRAINT_TYPE, '') != 'FOREIGN KEY'
  ORDER BY S.TABLE_NAME
        , S.INDEX_NAME
        , S.SEQ_IN_INDEX`

		let curIndexName = ''
		let table: DbTable | undefined
		let index: DbIndex
		const rows = await MariaDbUtil.query(conn, sSQL)
		rows.forEach((row: any) => {
			table = this.tableMap.get(row.TABLE_NAME)
			if (row.CONSTRAINT_TYPE === 'PRIMARY KEY') {
				//  Primary Key
				if (!table!.primaryKey) {
					table!.primaryKey = { columns: '' }
				}
				if (!table!.primaryKey!.columns) {
					table!.primaryKey!.columns = row.COLUMN_NAME
				} else {
					table!.primaryKey!.columns += `, ${row.COLUMN_NAME}`
				}
				// table!.primaryKey!.cluster = true
			} else {
				//  Index
				if (curIndexName !== row.INDEX_NAME) {
					index = { name: row.INDEX_NAME, columns: '' }
					table?.indexes.push(index)

					// index.cluster = false
					index.unique = row.NON_UNIQUE === 0

					curIndexName = row.INDEX_NAME
				}

				index.type = row.INDEX_TYPE

				if (!index.columns) {
					index.columns = row.COLUMN_NAME
				} else {
					index.columns += `, ${row.COLUMN_NAME}`
				}
			}
		})
	}

	private async RenderForeignKey(conn: Connection) {
		const sSQL: string = `SELECT R.TABLE_NAME
      , R.CONSTRAINT_NAME
      , R.UPDATE_RULE
      , R.DELETE_RULE
      , R.REFERENCED_TABLE_NAME
      , K.COLUMN_NAME
      , K.ORDINAL_POSITION
      , K.POSITION_IN_UNIQUE_CONSTRAINT
      , K.REFERENCED_COLUMN_NAME
  FROM information_schema.REFERENTIAL_CONSTRAINTS R
  JOIN information_schema.KEY_COLUMN_USAGE K
    ON  R.CONSTRAINT_SCHEMA = K.CONSTRAINT_SCHEMA
    AND R.TABLE_NAME        = K.TABLE_NAME
    AND R.CONSTRAINT_NAME   = K.CONSTRAINT_NAME
  WHERE R.CONSTRAINT_SCHEMA = DATABASE()
  ORDER BY R.TABLE_NAME
      , R.CONSTRAINT_NAME
      , K.ORDINAL_POSITION;`

		let curFKName = ''
		let table: DbTable | undefined
		let foreignKey: DbForeignKey
		const rows = await MariaDbUtil.query(conn, sSQL)
		rows.forEach((row: any) => {
			if (curFKName !== row.CONSTRAINT_NAME) {
				table = this.tableMap.get(row.TABLE_NAME)
				foreignKey = {
					name: row.CONSTRAINT_NAME,
					columns: '',
					refTable: row.REFERENCED_TABLE_NAME,
					refColumns: '',
					options: '',
				}
				table!.foreignKeys.push(foreignKey)

				if (row.DELETE_RULE !== 'RESTRICT') {
					foreignKey.options = `ON DELETE ${row.DELETE_RULE}`
				}
				if (row.UPDATE_RULE !== 'RESTRICT') {
					if (foreignKey.options) {
						foreignKey.options += ' '
					}
					foreignKey.options += `ON UPDATE ${row.UPDATE_RULE}`
				}

				curFKName = row.CONSTRAINT_NAME
			}

			if (foreignKey.columns) {
				foreignKey.columns += ', '
			}
			foreignKey.columns += row.COLUMN_NAME

			if (foreignKey.refColumns) {
				foreignKey.refColumns += ', '
			}
			foreignKey.refColumns += row.REFERENCED_COLUMN_NAME
		})
	}

	private async RenderColumn(conn: Connection, model: DbModel) {
		const sSQL: string = `SELECT C.TABLE_NAME
        , C.COLUMN_NAME
        , C.ORDINAL_POSITION
        , C.COLUMN_TYPE
        , C.COLUMN_DEFAULT
        , C.EXTRA
        , C.IS_NULLABLE
        , C.COLUMN_COMMENT
        , C.DATA_TYPE
        , C.CHARACTER_MAXIMUM_LENGTH
        , C.NUMERIC_PRECISION
        , C.NUMERIC_SCALE
        , C.CHARACTER_SET_NAME
        , C.COLLATION_NAME
        , T.TABLE_COMMENT
        , CCSA.CHARACTER_SET_NAME TABLE_CHARACTER_SET_NAME
        , T.TABLE_COLLATION
        , T.AUTO_INCREMENT 
				, T.ENGINE
    FROM information_schema.COLUMNS C
    JOIN information_schema.TABLES T
      ON T.TABLE_SCHEMA = C.TABLE_SCHEMA
      AND T.TABLE_NAME = C.TABLE_NAME
    JOIN information_schema.COLLATION_CHARACTER_SET_APPLICABILITY CCSA
      ON CCSA.COLLATION_NAME = T.TABLE_COLLATION
    WHERE C.TABLE_SCHEMA = DATABASE()
 	 ORDER BY C.TABLE_NAME
        , C.ORDINAL_POSITION`

		let prevTableName = ''
		let table: DbTable
		const rows = await MariaDbUtil.query(conn, sSQL)
		rows.forEach((row: any) => {
			if (prevTableName !== row.TABLE_NAME) {
				//  set Table
				prevTableName = row.TABLE_NAME
				table = this.createTable(prevTableName)
				table.comment = row.TABLE_COMMENT
				table.characterSet = row.TABLE_CHARACTER_SET_NAME
				table.collation = row.TABLE_COLLATION
				table.engine = row.ENGINE
				model.tables.push(table)
				this.tableMap.set(prevTableName, table)
			}

			// set Column
			const column: DbColumn = {
				label: this.labelIt(row.COLUMN_NAME),
				name: row.COLUMN_NAME,
				type: this.GetColumnDataType(
					row.DATA_TYPE,
					row.COLUMN_TYPE,
					row.EXTRA,
					row.CHARACTER_SET_NAME === row.TABLE_CHARACTER_SET_NAME ? '' : row.CHARACTER_SET_NAME,
					row.COLLATION_NAME === row.TABLE_COLLATION ? '' : row.COLLATION_NAME,
				),
				nullable: row.IS_NULLABLE === 'YES',
			}

			const vDefault = row.COLUMN_DEFAULT
			if (!vDefault || vDefault === 'NULL') {
				column.default = ''
			} else if (this.IsText(row.DATA_TYPE)) {
				column.default = vDefault
			} else {
				column.default = vDefault
			}

			column.comment = row.COLUMN_COMMENT
			table.columns.push(column)
		})
	}

	private GetColumnDataType(
		dataType: string,
		columnType: string,
		extra: string,
		charSet: string,
		collation: string,
	): string {
		let retDataType: string = columnType
		if (MariaDbImportProvider.DATA_TYPES_WITH_LENGTH.indexOf(dataType) === -1) {
			retDataType = columnType.replace(MariaDbImportProvider.REGEX_LEN_EXPR, '')
		}
		if (charSet) {
			retDataType = `${retDataType} CHARACTER SET ${charSet}`
		}
		if (collation) {
			retDataType = `${retDataType} COLLATE ${collation}`
		}
		if (extra) {
			retDataType = `${retDataType} ${extra}`
		}
		return retDataType
	}

	private IsText(dataType: string): boolean {
		let vIsText: boolean = false

		const lowerDataType = dataType.toLowerCase()
		if (lowerDataType.indexOf('char') >= 0 || lowerDataType.indexOf('text') >= 0) {
			vIsText = true
		}
		return vIsText
	}

	private labelIt(columnName: string): string {
		let label: string = ''
		let preIsLower = false
		for (let i = 0; i < columnName.length; i += 1) {
			const current = columnName.charAt(i)
			if (current === '_') {
				label += ` `
				preIsLower = false
				// eslint-disable-next-line no-continue
				continue
			}

			if (current.toUpperCase() === current) {
				if (preIsLower) {
					label += ` ${current}`
				} else {
					label += current
				}
				preIsLower = false
			} else {
				label += current
				preIsLower = true
			}
		}

		return label
	}
}
